

--隐私面单明细
--开发 侯文龙
--时间 ：2022-07-11

insert overwrite table jms_dm.dm_privacy_waybill_detail_dt partition(dt,type1)
--隐私面单明细
select /*+ broadcast(b),broadcast(terminal_secre),broadcast(agent_point) */
     subordinate_agent_code                                   --所属代理区code
    ,subordinate_agent_name                                   --所属代理区
    ,first_franchisee_code                                    --加盟商code
    ,first_franchisee_name                                    --加盟商
    ,code                                                     --网点编码
    ,name                                                     --网点名字
    ,a.waybill_no                                             --运单号
    ,a.scan_time                                              --签收/寄件时间
    ,a.customer_code                                          --客户编码
    ,a.customer_name                                          --客户名字
    ,a.deliver_later_user                                     --派件人员
    ,a.end_network_arrival_early_time                         --末端网点到件时间
    ,a.outstore_later_storecode                               --最晚出库门店编码
    ,agent_point.detailed_address                             --门店地址
    ,terminal_secre.business_id                               --品牌ID
    ,terminal_secre.business_name                             --品牌名字
    ,a.outstore_later_scansitecode                            --最后一次出库网点
    ,a.out_store_scan_time                                    --出库时间
    ,a.instore_later_scansitecode                             --最后一次入库网点
    ,a.instore_scan_time                                      --入库时间
    ,a.deliver_later_scansitecode                             --第一次派件网点
    ,a.deliver_scantime                                       --派件时间
    ,a.end_network_network_code                               --末端到件网点code
    ,Coalesce(a.sign_later_scantime,a.out_store_scan_time,a.instore_scan_time) as sign_later_scantime  --签收时间
    ,a.deliver_pistol_id                                      --操作设备
    ,ordersource_code                                        --多平台订单来源编码
    ,ordersource_name                                        --多平台订单来源名字
    ,a.phone_contact                                          --电联标识\ 1是, 0:否
    ,a.dt as date_time                                        --分区日期
    ,a.type1 as type2                                         --2签收 1寄件
    ,a.is_privacy  -- 是否隐私面单
    ,if(order_mark.waybill_id is not null ,1,0) as is_ali_order  --是否淘系
    ,a.dt                                                     --分区日期
    ,a.type1                                                  --2签收 1寄件

from (
--签收维度
select
    waybill_no
    ,end_network_arrival_early_time as scan_time          --签收时间时间
    ,deliver_later_scansitecode  as scan_site_code                              --签收网点时间
    ,customer_code         --客户编码
    ,customer_name         --客户名字
    ,deliver_later_user          --派件人员
    ,end_network_arrival_early_time    --末端网点到件时间
    ,outstore_later_storecode     --最晚出库门店编码
    ,outstore_later_scansitecode   --最后一次出库网点
    ,if(outstore_early_scansitecode=outstore_later_scansitecode,outstore_early_scantime,outstore_later_scantime) as out_store_scan_time --出库时间
    ,instore_later_scansitecode      --最后一次入库网点
    ,if(instore_early_scansitecode=instore_later_scansitecode,instore_early_scantime,instore_later_scantime) as instore_scan_time   --入库时间
    ,deliver_later_scansitecode     --第一次派件网点
    ,if(deliver_later_scansitecode=deliver_early_scansitecode,deliver_early_scantime,deliver_later_scantime) as deliver_scantime   --派件时间
    ,end_network_network_code       --末端到件网点code
    ,if(deliver_later_scansitecode=deliver_early_scansitecode,deliver_early_pistol_id,deliver_later_pistol_id) as deliver_pistol_id   --操作设备
    ,order_source_code_order as  ordersource_code                               --多平台订单来源编码
    ,order_source_name_order as  ordersource_name      --多平台订单来源名字
    ,phone_contact                  --电联标识\; 1:是, 0:否
    ,sign_later_scantime                 --签收时间
     ,'2' as type1                  --签收
     ,if(is_privacy in (1,2) ,1,0) as is_privacy

     ,to_date(end_network_arrival_early_time) as dt
from jms_dwd.dwd_wide_unsign_summary_waybill_dt
where dt>=date_add('{{ execution_date | cst_ds }}',-30)
and dt<='{{ execution_date | cst_ds }}'
and to_date(end_network_arrival_early_time)>date_add('{{ execution_date | cst_ds }}',-10)
and to_date(end_network_arrival_early_time)<='{{ execution_date | cst_ds }}'
-- and is_privacy in (1,2)     --是否是隐私件  1是 0否
--and deliver_later_scansitecode=end_network_network_code  --派件网点等与派件网点
union all
--寄件维度
select
    waybill_no
    ,taking_early_scantime as  scan_time           --寄件时间
    ,pick_network_code as  scan_site_code      --寄件网点
    ,customer_code         --客户编码
    ,customer_name         --客户名字
    ,null as deliver_later_user          --派件人员
    ,null as end_network_arrival_early_time    --末端网点到件时间
    ,null as outstore_later_storecode     --最晚出库门店编码
    ,null as outstore_later_scansitecode   --最后一次出库网点
    ,null as out_store_scan_time --出库时间
    ,null as instore_later_scansitecode      --最后一次入库网点
    ,null as instore_scan_time   --入库时间
    ,null as deliver_early_scansitecode     --第一次派件网点
    ,null as deliver_scantime   --派件时间
    ,null as end_network_network_code       --末端到件网点code
    ,null as deliver_pistol_id   --操作设备
    ,order_source_code_order                                --多平台订单来源编码
    ,order_source_name_order        --多平台订单来源名字
    ,phone_contact                  --电联标识\; 1:是, 0:否
    ,null as sign_later_scantime                 --签收时间
     ,'1' as type1                   --寄件
     ,if(is_privacy in (1,2) ,1,0) as is_privacy
     ,to_date(taking_early_scantime) as dt
from jms_dwd.dwd_wide_unsign_summary_waybill_dt
where dt>=date_add('{{ execution_date | cst_ds }}',-30)
and dt<='{{ execution_date | cst_ds }}'
and to_date(taking_early_scantime)>date_add('{{ execution_date | cst_ds }}',-10)
and to_date(taking_early_scantime)<='{{ execution_date | cst_ds }}'
-- and is_privacy in (1,2)         --是否是隐私件  1是 0否
) a
join (
select
       code                      --网点编码
      ,name                     --网点名字
      ,agent_name as subordinate_agent_name    --所属代理区
      ,agent_code as subordinate_agent_id     --所属代理区ID
      ,agent_code as subordinate_agent_code    --所属代理区code
      ,fran_code as first_franchisee_code     --加盟商code
      ,fran_name as first_franchisee_name     --加盟商
 from jms_dim.dim_network_whole_massage
    where is_enable=1
    and is_enable=1
    and network_type=6
) b
on a.scan_site_code=b.code
left join (
select
network_code, --网点编码
network_name,--网点名字
case when cnt>1 then '--' else detailed_address end as detailed_address,
courier_station_code,
case when cnt>1 then '--' else business_id end as business_id
from (select
       network_code   --网点编码
       ,max(network_name)as network_name  --网点名字
       ,max(detailed_address)as detailed_address  --门店地址
       ,courier_station_code  --门店关联code
       ,max(business_id)   as business_id,         --门店id
     count(1) as cnt
   from jms_dim.dim_tab_agent_point_base
   where status=1   --状态有效
   group by courier_station_code,network_code
   )tmp
) agent_point
on a.outstore_later_storecode=agent_point.courier_station_code
and a.scan_site_code=agent_point.network_code
--and a.scan_site_code=agent_point.network_code
left join (
       select
         business_id     --品牌ID
        ,business_name   --品牌名字
       from jms_dim.dim_tab_terminal_secret_base
      --  where dt='{{ execution_date | cst_ds }}'
) terminal_secre
on agent_point.business_id=terminal_secre.business_id
left join (
    select waybill_id
    from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
    where  dt>=date_sub('{{ execution_date | cst_ds }}',30)
    and dt<='{{ execution_date | cst_ds }}'
    and field_name='isAliOrder'
    and field_value='Y'
    distribute by  abs(hash(waybill_id)) % 300
    ) order_mark
    on a.waybill_no=order_mark.waybill_id
distribute by a.dt, abs(hash(a.waybill_no)) % 100
;